﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using pcsse.App_Code.DAL;
using System.Data.Common;

namespace pcsse.App_Code.entity
{
    public class import
    {
        OleDbConnection m_conn = null;

        public import()
        {

        }

        public bool connToFile( string fileanme )
        {
            try
            {
                String _filename = fileanme;
                String strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + _filename + ";" + "Extended Properties='Excel 12.0;HDR=NO'";
                m_conn = new OleDbConnection( strConn );
                return true;
            }
            catch ( System.Exception ex )
            {
                throw ex;
            }
        }

        public ArrayList GetTableList()
        {
            try
            {
                m_conn.Open();
                DataTable dt = m_conn.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" } );
                ArrayList list = new ArrayList();
                for ( int i = 0 ; i < dt.Rows.Count ; i++ )
                {
                    list.Add( dt.Rows[i][2].ToString() );
                }
                m_conn.Close();
                return list;
            }
            catch ( System.Exception ex )
            {
                throw ex;
            }
        }

        public DataSet GetData( string sheet, int mode )
        {
            try
            {
                m_conn.Open();
                String sql = "select * from [" + sheet + "]";
                OleDbDataAdapter da = new OleDbDataAdapter( sql, m_conn );
                DataSet ds = new DataSet();
                da.Fill( ds, sheet );
                m_conn.Close();
                if ( mode == 0 )
                {
                    if ( ds.Tables[0].Columns.Count == 8 )
                    {
                        ds.Tables[0].Columns[0].ColumnName = "NUMBER";
                        ds.Tables[0].Columns[1].ColumnName = "NAME";
                        ds.Tables[0].Columns[2].ColumnName = "GENDER";
                        ds.Tables[0].Columns[3].ColumnName = "NATIONALITY";
                        ds.Tables[0].Columns[4].ColumnName = "BIRTHDAY";
                        ds.Tables[0].Columns[5].ColumnName = "IDNUMBER";
                        ds.Tables[0].Columns[6].ColumnName = "TELEPHONE";
                        ds.Tables[0].Columns[7].ColumnName = "EMAIL";
                    }
                    else
                    {
                        return null;
                    }
                }
                else
                {
                    if ( ds.Tables[0].Columns.Count == 9 )
                    {
                        ds.Tables[0].Columns[0].ColumnName = "NUMBER";
                        ds.Tables[0].Columns[1].ColumnName = "APP_TIME";
                        ds.Tables[0].Columns[2].ColumnName = "ACTIVE_TIME";
                        ds.Tables[0].Columns[3].ColumnName = "TRAIN_TIME";
                        ds.Tables[0].Columns[4].ColumnName = "PRE_TIME";
                        ds.Tables[0].Columns[5].ColumnName = "CONN_NAME";
                        ds.Tables[0].Columns[6].ColumnName = "INTRO_NAME";
                        ds.Tables[0].Columns[7].ColumnName = "FULL_TIME";
                        ds.Tables[0].Columns[8].ColumnName = "STATE";
                    }
                    else
                    {
                        return null;
                    }
                }
                return ds;
            }
            catch ( System.Exception ex )
            {
                throw ex;
            }
        }
        public DataTable DataImport( string sheet, int branch_num )
        {
            try
            {
                //人员数据导入
                if ( branch_num == 0 )
                {
                    DAL.managementTableAdapters.T_PERSONINFOTableAdapter pa = new DAL.managementTableAdapters.T_PERSONINFOTableAdapter();
                    DataTable errtable = new DataTable();
                    errtable.Columns.Add( "NUMBER" );
                    errtable.Columns.Add( "ERR_INFO" );
                    DataSet ds = GetData( sheet, 0 );
                    for ( int i = 0 ; i < ds.Tables[0].Rows.Count ; i++ )
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        try
                        {
                            pa.InsertQuery( dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), Convert.ToDateTime( dr[4].ToString() ), dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), 2 );
                        }
                        catch ( System.Exception ex )
                        {
                            if ( ex is DbException )
                            {
                                try
                                {
                                    pcsse.App_Code.DAL.management.T_PERSONINFODataTable pdt = pa.GetDataByNumber( dr[0].ToString() );
                                    if ( pdt.Rows.Count == 1 )
                                    {
                                        int op = Convert.ToInt32( pdt.Rows[0]["PRIORITY_ID"].ToString() );
                                        int oi = Convert.ToInt32( pdt.Rows[0][0].ToString() );
                                        pa.UpdateQuery( dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), Convert.ToDateTime( dr[4].ToString() ), dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), op, oi );
                                        object[] param = new object[] { dr[0].ToString(), "该编号已经存在，已经更新数据" };
                                        errtable.Rows.Add( param );
                                    }
                                    else
                                    {
                                        object[] param = new object[] { dr[0].ToString(), "数据库错误，请与管理员联系" };
                                        errtable.Rows.Add( param );
                                    }
                                }
                                catch ( System.Exception inex )
                                {
                                    if ( inex is DbException )
                                    {
                                        object[] param = new object[] { dr[0].ToString(), "数据库错误，请与管理员联系" };
                                        errtable.Rows.Add( param );
                                    }
                                    else if ( inex is NoNullAllowedException )
                                    {
                                        object[] param = new object[] { dr[0].ToString(), "不允许有空值存在" };
                                        errtable.Rows.Add( param );
                                    }
                                    else if ( inex is ArgumentException )
                                    {
                                        object[] param = new object[] { dr[0].ToString(), "一个或多个参数不合法" };
                                        errtable.Rows.Add( param );
                                    }
                                    else if ( inex is FormatException )
                                    {
                                        object[] param = new object[] { dr[0].ToString(), "一个或多个参数格式不合法" };
                                        errtable.Rows.Add( param );
                                    }
                                    else
                                    {
                                        object[] param = new object[] { dr[0].ToString(), "未知错误" };
                                        errtable.Rows.Add( param );
                                    }
                                }
                            }
                            else if ( ex is NoNullAllowedException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "不允许有空值存在" };
                                errtable.Rows.Add( param );
                            }
                            else if ( ex is ArgumentException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "一个或多个参数不合法" };
                                errtable.Rows.Add( param );
                            }
                            else if ( ex is FormatException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "一个或多个参数格式不合法" };
                                errtable.Rows.Add( param );
                            }
                            else
                            {
                                object[] param = new object[] { dr[0].ToString(), "未知错误" };
                                errtable.Rows.Add( param );
                            }
                        }
                    }
                    return errtable;
                }
                else
                //党员数据导入
                {
                    DAL.managementTableAdapters.T_PERSONINFOTableAdapter pa = new DAL.managementTableAdapters.T_PERSONINFOTableAdapter();
                    DAL.managementTableAdapters.T_PARTYTableAdapter pta = new DAL.managementTableAdapters.T_PARTYTableAdapter();
                    DataTable errtable = new DataTable();
                    errtable.Columns.Add( "NUMBER" );
                    errtable.Columns.Add( "ERR_INFO" );
                    DataSet ds = GetData( sheet, 1 );
                    for ( int i = 0 ; i < ds.Tables[0].Rows.Count ; i++ )
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        try
                        {
                            DAL.management.T_PERSONINFODataTable pdt = pa.GetDataByNumber( dr[0].ToString() );
                            if ( pdt.Rows.Count == 0 )
                            {
                                object[] param = new object[] { dr[0].ToString(), "该编号不存在，请首先插入人员信息" };
                                errtable.Rows.Add( param );
                                continue;
                            }
                            else
                            {
                                DAL.management.T_PARTYDataTable padt = pta.GetDataByPersonID( Convert.ToInt32( pdt.Rows[0]["ID"] ) );
                                DAL.managementTableAdapters.T_STATETableAdapter sta = new DAL.managementTableAdapters.T_STATETableAdapter();
                                DAL.management.T_STATEDataTable sdt = sta.GetDataByText( dr[8].ToString() );
                                if ( sdt.Rows.Count == 0 )
                                {
                                    object[] param = new object[] { dr[0].ToString(), "状态不存在，请确认状态是否正确" };
                                    errtable.Rows.Add( param );
                                    continue;
                                }
                                else
                                {
                                    int stateid = Convert.ToInt32( sdt.Rows[0]["STATE_ID"].ToString() );
                                    DateTime[] param = new DateTime[4];
                                    DateTime param7 = DateTime.Today;
                                    for ( int ptr = 1 ; ptr <= 4 ; ptr++ )
                                    {
                                        if ( !string.IsNullOrEmpty( dr[ptr].ToString() ) )
                                        {
                                            param[ptr - 1] = Convert.ToDateTime( dr[ptr].ToString() );
                                        }
                                        else
                                        {
                                            param[ptr - 1] = DateTime.Today;
                                        }
                                    }
                                    if ( !string.IsNullOrEmpty( dr[7].ToString() ) )
                                    {
                                        param7 = Convert.ToDateTime( dr[7].ToString() );
                                    }
                                    pta.UpdateQuery( param[0], param[1], param[2], param[3], dr[5].ToString(), dr[6].ToString(), param7, branch_num, stateid, Convert.ToInt32( padt.Rows[0]["ID"] ) );
                                }
                            }
                        }
                        catch ( System.Exception ex )
                        {
                            if ( ex is DbException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "该编号已经存在" };
                                errtable.Rows.Add( param );
                            }
                            else if ( ex is NoNullAllowedException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "不允许有空值存在" };
                                errtable.Rows.Add( param );
                            }
                            else if ( ex is ArgumentException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "一个或多个参数不合法" };
                                errtable.Rows.Add( param );
                            }
                            else if ( ex is FormatException )
                            {
                                object[] param = new object[] { dr[0].ToString(), "数据格式错误，请检查日期格式是否正确" };
                                errtable.Rows.Add( param );
                            }
                            else
                            {
                                object[] param = new object[] { dr[0].ToString(), "未知错误" };
                                errtable.Rows.Add( param );
                            }
                        }
                    }
                    return errtable;
                }
            }
            catch ( System.Exception ex )
            {
                throw ex;
            }
        }
    }
}